use master
go

if db_id('ProjectSem3') is not null
drop database ProjectSem3
go

CREATE DATABASE ProjectSem3
go
Use ProjectSem3
go

CREATE TABLE tb_User
(
	Username varchar(50) primary key,
	[Password] varchar(50),
	[Address] varchar(50),
	Phone varchar(12),
	Gender bit,
	[Status] bit
)
go

CREATE TABLE tb_Services
(
	Id int identity(1,1) primary key,
	Name varchar(50),
	[Status] bit
)

CREATE TABLE tb_User_Service
(
	Username varchar(50) FOREIGN KEY REFERENCES tb_User(Username),
	ServiceId int FOREIGN KEY REFERENCES tb_Services(Id),
	CONSTRAINT PF_User_Service PRIMARY KEY (Username,ServiceId)
)
go

CREATE TABLE tb_Payment
(
	Id int identity(1,1) primary key,
	UserId varchar(50) FOREIGN KEY REFERENCES tb_User(Username),
	ServiceId int FOREIGN KEY REFERENCES tb_Services(Id),
	[DateTime] smalldatetime,
	Amount money,
	Phone varchar(12),
	[Status] bit
) 

go
CREATE TABLE tb_Feedback
(
	Id int identity(1,1) primary key,
	Email varchar(50),
	Title varchar(500),
	Content text,
	[Status] bit,
	reply varchar(max)
)
go
CREATE TABLE tb_CustomerCare
(
	Id int identity(1,1) primary key,
	Username varchar(50) FOREIGN KEY REFERENCES tb_User(Username),
	Title varchar(500),
	Content text,
	[Status] bit,
	reply varchar(max)
)
go
CREATE TABLE tb_Admin
(
	Username varchar(50) primary key,
	[Password] varchar(50),
	[Status] bit
)
go
create proc sp_viewBill
as
SELECT     tb_Payment.Id, tb_Services.Name, tb_User.Username, tb_Payment.DateTime, tb_Payment.Amount, tb_Payment.Phone, tb_Payment.Status
FROM         tb_Payment INNER JOIN
                      tb_Services ON tb_Payment.ServiceId = tb_Services.Id INNER JOIN
                      tb_User ON tb_Payment.UserId = tb_User.Username
go
create proc sp_viewBillByUName
@Username varchar(50)
as
SELECT     tb_Payment.Id, tb_Services.Name, tb_User.Username, tb_Payment.DateTime, tb_Payment.Amount, tb_Payment.Phone, tb_Payment.Status
FROM         tb_Payment INNER JOIN
                      tb_Services ON tb_Payment.ServiceId = tb_Services.Id INNER JOIN
                      tb_User ON tb_Payment.UserId = tb_User.Username
Where tb_User.Username=@Username
insert into tb_Admin values('admin','123',1)
go
insert into tb_User values('user','password','address','0156772372',1,1)
insert into tb_User values('user1','password','address','0156772372',1,1)
insert into tb_User values('user2','password','address','0156772372',1,1)
insert into tb_User values('user3','password','address','0156772372',1,1)
insert into tb_User values('user4','password','address','0156772372',1,1)
go
insert into tb_Feedback values('email@email.com','title','content',0,null)
insert into tb_Feedback values('email@email.com','title','content',0,null)
insert into tb_Feedback values('email@email.com','title','content',0,null)
insert into tb_Feedback values('email@email.com','title','content',0,null)
go
insert into tb_CustomerCare values('user1','title','content',0,null)
insert into tb_CustomerCare values('user1','title','content',0,null)
insert into tb_CustomerCare values('user2','title','content',0,null)
insert into tb_CustomerCare values('user3','title','content',0,null)
go
insert into tb_Services values('rechange online',0)
go
insert into tb_Payment values('user',1,'2012/12/20',10,'0156772372',1)
insert into tb_Payment values('user1',1,'2013/06/20',10,'0156772372',1)
insert into tb_Payment values('user2',1,'2010/04/20',10,'0156772372',1)
insert into tb_Payment values('user3',1,'2011/01/20',10,'0156772372',1)
insert into tb_Payment values('user4',1,'2005/03/23',10,'0156772372',1)
insert into tb_Payment values('user',1,'2012/12/20',10,'0156772372',1)
insert into tb_Payment values('user1',1,'2013/06/20',10,'0156772372',1)
insert into tb_Payment values('user2',1,'2010/04/20',10,'0156772372',1)
insert into tb_Payment values('user3',1,'2011/01/20',10,'0156772372',1)
insert into tb_Payment values('user4',1,'2005/03/23',10,'0156772372',1)
insert into tb_Payment values('user',1,'2012/12/20',10,'0156772372',1)
insert into tb_Payment values('user1',1,'2013/06/20',10,'0156772372',1)
insert into tb_Payment values('user2',1,'2010/04/20',10,'0156772372',1)
insert into tb_Payment values('user3',1,'2011/01/20',10,'0156772372',1)
insert into tb_Payment values('user4',1,'2005/03/23',10,'0156772372',1)
go
if exists (select * from dbo.sysobjects where name = 'sp_tb_Admin_Delete')
	drop procedure [dbo].[sp_tb_Admin_Delete]
Go 
CREATE PROCEDURE dbo.sp_tb_Admin_Delete
@Username	varchar(50)
AS
Delete FROM tb_Admin WHERE  Username= @Username
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_Admin_GetByAll')
	drop procedure [dbo].[sp_tb_Admin_GetByAll]
Go 
CREATE PROCEDURE dbo.sp_tb_Admin_GetByAll
AS
	SELECT * FROM tb_Admin
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_Admin_GetById')
	drop procedure [dbo].[sp_tb_Admin_GetById]
Go 
CREATE PROCEDURE dbo.sp_tb_Admin_GetById
@Username	varchar(50)
AS
SELECT * FROM tb_Admin WHERE  Username= @Username
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_Admin_GetByTop')
	drop procedure [dbo].[sp_tb_Admin_GetByTop]
Go 
CREATE PROCEDURE dbo.sp_tb_Admin_GetByTop
@Top nvarchar(10),
@Where nvarchar(500), 
@Order nvarchar(500)
AS
	Declare @SQL as nvarchar(500) 
Select @SQL = 'SELECT top (' + @Top + ') * FROM [tb_Admin]'
if len(@Top) = 0
BEGIN
Select @SQL = 'SELECT * FROM [tb_Admin]'
END
if len(@Where) >0
BEGIN
Select @SQL = @SQL + ' Where ' + @Where
END
if len(@Order) >0
BEGIN
Select @SQL = @SQL + ' Order by ' + @Order
END
EXEC (@SQL)
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_Admin_Insert')
	drop procedure [dbo].[sp_tb_Admin_Insert]
Go 
CREATE PROCEDURE dbo.sp_tb_Admin_Insert
@Username	varchar(50),
@Password	varchar(50),
@Status	bit
AS
INSERT INTO tb_Admin([Username],[Password],[Status]) VALUES(@Username ,@Password ,@Status )
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_Admin_Update')
	drop procedure [dbo].[sp_tb_Admin_Update]
Go 
CREATE PROCEDURE dbo.sp_tb_Admin_Update
@Username	varchar(50),
@Password	varchar(50),
@Status	bit
AS
UPDATE tb_Admin SET [Password] = @Password,[Status] = @Status
WHERE  Username= @Username
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_Feedback_Delete')
	drop procedure [dbo].[sp_tb_Feedback_Delete]
Go 
CREATE PROCEDURE dbo.sp_tb_Feedback_Delete
@Id	int
AS
Delete FROM tb_Feedback WHERE  Id= @Id
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_Feedback_GetByAll')
	drop procedure [dbo].[sp_tb_Feedback_GetByAll]
Go 
CREATE PROCEDURE dbo.sp_tb_Feedback_GetByAll
AS
	SELECT * FROM tb_Feedback
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_Feedback_GetById')
	drop procedure [dbo].[sp_tb_Feedback_GetById]
Go 
CREATE PROCEDURE dbo.sp_tb_Feedback_GetById
@Id	int
AS
SELECT * FROM tb_Feedback WHERE  Id= @Id
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_Feedback_GetByTop')
	drop procedure [dbo].[sp_tb_Feedback_GetByTop]
Go 
CREATE PROCEDURE dbo.sp_tb_Feedback_GetByTop
@Top nvarchar(10),
@Where nvarchar(500), 
@Order nvarchar(500)
AS
	Declare @SQL as nvarchar(500) 
Select @SQL = 'SELECT top (' + @Top + ') * FROM [tb_Feedback]'
if len(@Top) = 0
BEGIN
Select @SQL = 'SELECT * FROM [tb_Feedback]'
END
if len(@Where) >0
BEGIN
Select @SQL = @SQL + ' Where ' + @Where
END
if len(@Order) >0
BEGIN
Select @SQL = @SQL + ' Order by ' + @Order
END
EXEC (@SQL)
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_Feedback_Insert')
	drop procedure [dbo].[sp_tb_Feedback_Insert]
Go 
CREATE PROCEDURE dbo.sp_tb_Feedback_Insert
@Email	varchar(50),
@Title	varchar(500),
@Content	text,
@Status	bit,
@reply	text
AS
INSERT INTO tb_Feedback([Email],[Title],[Content],[Status],[reply]) VALUES(@Email ,@Title ,@Content ,@Status ,@reply )
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_Feedback_Update')
	drop procedure [dbo].[sp_tb_Feedback_Update]
Go 
CREATE PROCEDURE dbo.sp_tb_Feedback_Update
@Id	int,
@Email	varchar(50),
@Title	varchar(500),
@Content	text,
@Status	bit,
@reply	text
AS
UPDATE tb_Feedback SET [Email] = @Email,[Title] = @Title,[Content] = @Content,[Status] = @Status,[reply] = @reply
WHERE  Id= @Id
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_Payment_Delete')
	drop procedure [dbo].[sp_tb_Payment_Delete]
Go 
if exists (select * from dbo.sysobjects where name = 'sp_tb_CustomerCare_Delete')
	drop procedure [dbo].[sp_tb_CustomerCare_Delete]
Go 
CREATE PROCEDURE dbo.sp_tb_CustomerCare_Delete
@Id	int
AS
Delete FROM tb_CustomerCare WHERE  Id= @Id
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_CustomerCare_GetByAll')
	drop procedure [dbo].[sp_tb_CustomerCare_GetByAll]
Go 
CREATE PROCEDURE dbo.sp_tb_CustomerCare_GetByAll
AS
	SELECT * FROM tb_CustomerCare
Go
if exists (select * from dbo.sysobjects where name = 'tb_CustomerCare_GetByUserName')
	drop procedure [dbo].tb_CustomerCare_GetByUserName
Go 
CREATE PROCEDURE dbo.tb_CustomerCare_GetByUserName
@Username varchar(50)
AS
SELECT * FROM tb_CustomerCare WHERE  Username= @Username
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_CustomerCare_GetById')
	drop procedure [dbo].[sp_tb_CustomerCare_GetById]
Go 
CREATE PROCEDURE dbo.sp_tb_CustomerCare_GetById
@Id	int
AS
SELECT * FROM tb_CustomerCare WHERE  Id= @Id
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_CustomerCare_GetByTop')
	drop procedure [dbo].[sp_tb_CustomerCare_GetByTop]
Go 
CREATE PROCEDURE dbo.sp_tb_CustomerCare_GetByTop
@Top nvarchar(10),
@Where nvarchar(500), 
@Order nvarchar(500)
AS
	Declare @SQL as nvarchar(500) 
Select @SQL = 'SELECT top (' + @Top + ') * FROM [tb_CustomerCare]'
if len(@Top) = 0
BEGIN
Select @SQL = 'SELECT * FROM [tb_CustomerCare]'
END
if len(@Where) >0
BEGIN
Select @SQL = @SQL + ' Where ' + @Where
END
if len(@Order) >0
BEGIN
Select @SQL = @SQL + ' Order by ' + @Order
END
EXEC (@SQL)
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_CustomerCare_Insert')
	drop procedure [dbo].[sp_tb_CustomerCare_Insert]
Go 
CREATE PROCEDURE dbo.sp_tb_CustomerCare_Insert
@Email	varchar(50),
@Title	varchar(500),
@Content	text,
@Status	bit,
@reply	text
AS
INSERT INTO tb_CustomerCare([Username],[Title],[Content],[Status],[reply]) VALUES(@Email ,@Title ,@Content ,@Status ,@reply )
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_CustomerCare_Update')
	drop procedure [dbo].[sp_tb_CustomerCare_Update]
Go 
CREATE PROCEDURE dbo.sp_tb_CustomerCare_Update
@Id	int,
@Email	varchar(50),
@Title	varchar(500),
@Content	text,
@Status	bit,
@reply	text
AS
UPDATE tb_CustomerCare SET [Username] = @Email,[Title] = @Title,[Content] = @Content,[Status] = @Status,[reply] = @reply
WHERE  Id= @Id
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_Payment_Delete')
	drop procedure [dbo].[sp_tb_Payment_Delete]
Go 
CREATE PROCEDURE dbo.sp_tb_Payment_Delete
@Id	int
AS
Delete FROM tb_Payment WHERE  Id= @Id
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_Payment_GetByServiceId')
	drop procedure [dbo].[sp_tb_Payment_GetByServiceId]
Go 
CREATE PROCEDURE dbo.sp_tb_Payment_GetByServiceId
@ServiceId int
AS
	SELECT * FROM tb_Payment
 WHERE ServiceId = @ServiceId 
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_Payment_GetByUserId')
	drop procedure [dbo].[sp_tb_Payment_GetByUserId]
Go 
CREATE PROCEDURE dbo.sp_tb_Payment_GetByUserId
@UserId varchar(50)
AS
	SELECT * FROM tb_Payment
 WHERE UserId = @UserId 
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_Payment_GetByAll')
	drop procedure [dbo].[sp_tb_Payment_GetByAll]
Go 
CREATE PROCEDURE dbo.sp_tb_Payment_GetByAll
AS
	SELECT * FROM tb_Payment
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_Payment_GetByTop')
	drop procedure [dbo].[sp_tb_Payment_GetByTop]
Go 
CREATE PROCEDURE dbo.sp_tb_Payment_GetByTop
@Top nvarchar(10),
@Where nvarchar(500), 
@Order nvarchar(500)
AS
	Declare @SQL as nvarchar(500) 
Select @SQL = 'SELECT top (' + @Top + ') * FROM [tb_Payment]'
if len(@Top) = 0
BEGIN
Select @SQL = 'SELECT * FROM [tb_Payment]'
END
if len(@Where) >0
BEGIN
Select @SQL = @SQL + ' Where ' + @Where
END
if len(@Order) >0
BEGIN
Select @SQL = @SQL + ' Order by ' + @Order
END
EXEC (@SQL)
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_Payment_GetById')
	drop procedure [dbo].[sp_tb_Payment_GetById]
Go 
CREATE PROCEDURE dbo.sp_tb_Payment_GetById
@Id	int
AS
SELECT * FROM tb_Payment WHERE  Id= @Id
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_Payment_Update')
	drop procedure [dbo].[sp_tb_Payment_Update]
Go 
CREATE PROCEDURE dbo.sp_tb_Payment_Update
@Id	int,
@UserId	varchar(50),
@ServiceId	int,
@DateTime	smalldatetime,
@Amount	money,
@Phone	varchar(12),
@Status	bit
AS
UPDATE tb_Payment SET [UserId] = @UserId,[ServiceId] = @ServiceId,[DateTime] = @DateTime,[Amount] = @Amount,[Phone] = @Phone,[Status] = @Status
WHERE  Id= @Id
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_Payment_Insert')
	drop procedure [dbo].[sp_tb_Payment_Insert]
Go 
CREATE PROCEDURE dbo.sp_tb_Payment_Insert
@UserId	varchar(50),
@ServiceId	int,
@DateTime	smalldatetime,
@Amount	money,
@Phone	varchar(12),
@Status	bit
AS
INSERT INTO tb_Payment([UserId],[ServiceId],[DateTime],[Amount],[Phone],[Status]) VALUES(@UserId ,@ServiceId ,@DateTime ,@Amount ,@Phone ,@Status )
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_Services_Delete')
	drop procedure [dbo].[sp_tb_Services_Delete]
Go 
CREATE PROCEDURE dbo.sp_tb_Services_Delete
@Id	int
AS
Delete FROM tb_Services WHERE  Id= @Id
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_Services_GetByAll')
	drop procedure [dbo].[sp_tb_Services_GetByAll]
Go 
CREATE PROCEDURE dbo.sp_tb_Services_GetByAll
AS
	SELECT * FROM tb_Services
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_Services_GetById')
	drop procedure [dbo].[sp_tb_Services_GetById]
Go 
CREATE PROCEDURE dbo.sp_tb_Services_GetById
@Id	int
AS
SELECT * FROM tb_Services WHERE  Id= @Id
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_Services_GetByTop')
	drop procedure [dbo].[sp_tb_Services_GetByTop]
Go 
CREATE PROCEDURE dbo.sp_tb_Services_GetByTop
@Top nvarchar(10),
@Where nvarchar(500), 
@Order nvarchar(500)
AS
	Declare @SQL as nvarchar(500) 
Select @SQL = 'SELECT top (' + @Top + ') * FROM [tb_Services]'
if len(@Top) = 0
BEGIN
Select @SQL = 'SELECT * FROM [tb_Services]'
END
if len(@Where) >0
BEGIN
Select @SQL = @SQL + ' Where ' + @Where
END
if len(@Order) >0
BEGIN
Select @SQL = @SQL + ' Order by ' + @Order
END
EXEC (@SQL)
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_Services_Insert')
	drop procedure [dbo].[sp_tb_Services_Insert]
Go 
CREATE PROCEDURE dbo.sp_tb_Services_Insert
@Name	varchar(50),
@Status	bit
AS
INSERT INTO tb_Services([Name],[Status]) VALUES(@Name ,@Status )
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_Services_Update')
	drop procedure [dbo].[sp_tb_Services_Update]
Go 
CREATE PROCEDURE dbo.sp_tb_Services_Update
@Id	int,
@Name	varchar(50),
@Status	bit
AS
UPDATE tb_Services SET [Name] = @Name,[Status] = @Status
WHERE  Id= @Id
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_User_Delete')
	drop procedure [dbo].[sp_tb_User_Delete]
Go 
CREATE PROCEDURE dbo.sp_tb_User_Delete
@Username	varchar(50)
AS
Delete FROM tb_User WHERE  Username= @Username
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_User_GetByTop')
	drop procedure [dbo].[sp_tb_User_GetByTop]
Go 
CREATE PROCEDURE dbo.sp_tb_User_GetByTop
@Top nvarchar(10),
@Where nvarchar(500), 
@Order nvarchar(500)
AS
	Declare @SQL as nvarchar(500) 
Select @SQL = 'SELECT top (' + @Top + ') * FROM [tb_User]'
if len(@Top) = 0
BEGIN
Select @SQL = 'SELECT * FROM [tb_User]'
END
if len(@Where) >0
BEGIN
Select @SQL = @SQL + ' Where ' + @Where
END
if len(@Order) >0
BEGIN
Select @SQL = @SQL + ' Order by ' + @Order
END
EXEC (@SQL)
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_User_GetById')
	drop procedure [dbo].[sp_tb_User_GetById]
Go 
CREATE PROCEDURE dbo.sp_tb_User_GetById
@Username	varchar(50)
AS
SELECT * FROM tb_User WHERE  Username= @Username
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_User_GetByAll')
	drop procedure [dbo].[sp_tb_User_GetByAll]
Go 
CREATE PROCEDURE dbo.sp_tb_User_GetByAll
AS
	SELECT * FROM tb_User
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_User_Service_Delete')
	drop procedure [dbo].[sp_tb_User_Service_Delete]
Go 
CREATE PROCEDURE dbo.sp_tb_User_Service_Delete
@Username	varchar(50),
@ServiceId	int
AS
Delete FROM tb_User_Service WHERE  Username= @Username AND ServiceId= @ServiceId AND ServiceId= @ServiceId
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_User_Insert')
	drop procedure [dbo].[sp_tb_User_Insert]
Go 
CREATE PROCEDURE dbo.sp_tb_User_Insert
@Username	varchar(50),
@Password	varchar(50),
@Address	varchar(50),
@Phone	varchar(12),
@Gender	bit,
@Status	bit
AS
INSERT INTO tb_User([Username],[Password],[Address],[Phone],[Gender],[Status]) VALUES(@Username ,@Password ,@Address ,@Phone ,@Gender ,@Status )
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_User_Service_GetByAll')
	drop procedure [dbo].[sp_tb_User_Service_GetByAll]
Go 
CREATE PROCEDURE dbo.sp_tb_User_Service_GetByAll
AS
	SELECT * FROM tb_User_Service
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_User_Service_GetByServiceId')
	drop procedure [dbo].[sp_tb_User_Service_GetByServiceId]
Go 
CREATE PROCEDURE dbo.sp_tb_User_Service_GetByServiceId
@ServiceId int
AS
	SELECT * FROM tb_User_Service
 WHERE ServiceId = @ServiceId 
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_User_Service_GetByUsername')
	drop procedure [dbo].[sp_tb_User_Service_GetByUsername]
Go 
CREATE PROCEDURE dbo.sp_tb_User_Service_GetByUsername
@Username varchar(50)
AS
	SELECT * FROM tb_User_Service
 WHERE Username = @Username 
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_User_Service_GetByTop')
	drop procedure [dbo].[sp_tb_User_Service_GetByTop]
Go 
CREATE PROCEDURE dbo.sp_tb_User_Service_GetByTop
@Top nvarchar(10),
@Where nvarchar(500), 
@Order nvarchar(500)
AS
	Declare @SQL as nvarchar(500) 
Select @SQL = 'SELECT top (' + @Top + ') * FROM [tb_User_Service]'
if len(@Top) = 0
BEGIN
Select @SQL = 'SELECT * FROM [tb_User_Service]'
END
if len(@Where) >0
BEGIN
Select @SQL = @SQL + ' Where ' + @Where
END
if len(@Order) >0
BEGIN
Select @SQL = @SQL + ' Order by ' + @Order
END
EXEC (@SQL)
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_User_Service_GetById')
	drop procedure [dbo].[sp_tb_User_Service_GetById]
Go 
CREATE PROCEDURE dbo.sp_tb_User_Service_GetById
@Username	varchar(50),
@ServiceId	int
AS
SELECT * FROM tb_User_Service WHERE  Username= @Username AND ServiceId= @ServiceId AND ServiceId= @ServiceId
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_User_Service_Update')
	drop procedure [dbo].[sp_tb_User_Service_Update]
Go 
CREATE PROCEDURE dbo.sp_tb_User_Service_Insert
@Username	varchar(50),
@ServiceId	int
AS
INSERT INTO tb_User_Service([Username],[ServiceId]) VALUES(@Username ,@ServiceId )
Go
if exists (select * from dbo.sysobjects where name = 'sp_tb_User_Update')
	drop procedure [dbo].[sp_tb_User_Update]
Go 
CREATE PROCEDURE dbo.sp_tb_User_Update
@Username	varchar(50),
@Password	varchar(50),
@Address	varchar(50),
@Phone	varchar(12),
@Gender	bit,
@Status	bit
AS
UPDATE tb_User SET [Password] = @Password,[Address] = @Address,[Phone] = @Phone,[Gender] = @Gender,[Status] = @Status
WHERE  Username= @Username
Go
